package com.dashboard.giangday;

import java.sql.ResultSet;

import com.lb.jwitter.connectDatabase.ConnectTTGD;
import com.opensymphony.xwork2.Action;

public class ThongKeSoLanAction {
	private String jsonData;
	public String getJsonData() {
		return jsonData;
	}


	public void setJsonData(String jsonData) {
		this.jsonData = jsonData;
	}
	////////////////////////////////////////////////////////
	private String namHoc;

	public String getNamHoc() {
		return namHoc;
	}


	public void setNamHoc(String namHoc) {
		this.namHoc = namHoc;
	}
	/////////////////////////////////////////////////////////
	private String hocKy;

	public String getHocKy() {
		return hocKy;
	}


	public void setHocKy(String hocKy) {
		this.hocKy = hocKy;
	}
	/////////////////////////////////////////////////////////

	private String khoa;
	public String getKhoa() {
	return khoa;
	}
	
	
	public void setKhoa(String khoa) {
	this.khoa = khoa;
	}
	/////////////////////////////////////////////////////////
	private String boMon;
	
	public String getBoMon() {
	return boMon;
	}
	
	
	public void setBoMon(String boMon) {
	this.boMon = boMon;
	}
	////////////////////////////////////////////////////////
	public String execute() throws Exception
	{
		ConnectTTGD connectionOracle= new ConnectTTGD();
		if(connectionOracle.getConnection()!=null){
			//namHoc="2013-2014";
			//hocKy="1";
			//khoa="MT";
			//if(namHoc==null||namHoc.length()<1){
			//	return Action.ERROR;
			//}
			
			if(hocKy==null||hocKy.length()<1){
				return Action.ERROR;
			}
			if(khoa==null||khoa.length()<1){
				return Action.ERROR;
			}
			
			//hocKy  20141
			int nam = Integer.parseInt(hocKy.substring(0, 4));
			namHoc = nam+"-"+ (nam+1);
			hocKy = hocKy.substring(4);
			
			int tongSolan=0;
			int somTre=0;
			int ndcb=0;
			int ndkb=0;
			int db=0;
			int chuadb=0;
			int dunggio=0;
			String query="";
			
			//Tinh so lan som/tre, nghi day co bao, nghi day khong bao, day bu theo bo mon
			if(boMon!=null&&boMon.length()>0){
				 query="select maGhichu, count(maGhichu) as solan from sukiengiangday_view"
							+" where namhoc='"+namHoc+"' and hocky="+hocKy+" and maBoMon='"+boMon+"' and maGhichu<>'DTCB' and maGhichu<>'DTKB' "
							+" group by maGhichu";
			}
			
			//Tinh so lan som/tre, nghi day co bao, nghi day khong bao, day bu theo khoa
			else{
				 query="select maGhichu, count(maGhichu) as solan from sukiengiangday_view"
							+" where namhoc='"+namHoc+"' and hocky="+hocKy+" and maKhoa='"+khoa+"' and maGhichu<>'DTCB' and maGhichu<>'DTKB' "
							+" group by maGhichu";
			}
			
			ResultSet data=connectionOracle.getData(query);
			if(data!=null){
				while(data.next()){
					String ghiChu=data.getString("maGhichu");
					int solan=data.getInt("solan");
					//So lan day bu
					if(ghiChu.equals("DB")){
						db=solan;
					}
					//So lan nghi day co bao
					else if(ghiChu.equals("NDCB")){
						ndcb=solan;
					}
					
					//So lan nghi day khong bao
					else if(ghiChu.equals("NDKB")){
						ndkb=solan;			
					}
					
					//So lan vao tre
					else if(ghiChu.equals("VT")){
						somTre=somTre+solan;
					}
					
					//So lan ve som
					else if(ghiChu.equals("RS")){
						somTre=somTre+solan;
					}
					
					//So lan nghi giai lao qua gio
					else if(ghiChu.equals("NGLQG")){
						somTre=somTre+solan;
					}
				}
				//Tinh so lan chua day bu
				chuadb=(ndcb+ndkb)-db;
				if(chuadb<0){
					chuadb=0;
				}
			}
			
			//Tinh tong so buoi len lop cua cac giang vien theo bo mon
			if(boMon!=null&&boMon.length()>0){
				query="select sum(tongSoBuoi) as tongSolan from"
						+" (select ((A.sotietMonhoc/A.sotietTrongTuan)* A.soBuoiTrongTuan) as tongSoBuoi from"
						+" (select   maMonHoc, tenMonhoc,sotietMonhoc,lophoc, maCb,tenCb,sum(sotiet) as sotietTrongTuan, count(thu) as soBuoiTrongTuan"
						+" from thoikhoabieu_view"
						+" where namhoc='"+namHoc+"' and hocky="+hocKy+" and maBoMon='"+boMon+"'"
						+" group by  maMonHoc, tenMonhoc,sotietMonhoc,lophoc, maCb,tenCb"
						+" order by maMonHoc,maCb,lophoc,thu) A) B";
			}
			
			//Tinh tong so buoi len lop cua cac giang vien theo khoa
			else{
				query="select sum(tongSoBuoi) as tongSolan from"
						+" (select ((A.sotietMonhoc/A.sotietTrongTuan)* A.soBuoiTrongTuan) as tongSoBuoi from"
						+" (select   maMonHoc, tenMonhoc,sotietMonhoc,lophoc, maCb,tenCb,sum(sotiet) as sotietTrongTuan, count(thu) as soBuoiTrongTuan"
						+" from thoikhoabieu_view"
						+" where namhoc='"+namHoc+"' and hocky="+hocKy+" and maKhoa='"+khoa+"'"
						+" group by  maMonHoc, tenMonhoc,sotietMonhoc,lophoc, maCb,tenCb"
						+" order by maMonHoc,maCb,lophoc,thu) A) B";
			}
			
			data=connectionOracle.getData(query);
			if(data!=null){
				if(data.next()){
					//Tinh tong so buoi len lop cua cac giang vien
					tongSolan=data.getInt("tongSolan");
				}
			}
			
			//Tinh so lan dung gio cua cac giang vien
			dunggio=tongSolan-somTre;
			
			//{"tongSolan":10000,"dungGio":9000,"somTre":1000,"NDCB":200,"NDKB":100,"DB":250,"CDB":50}
			jsonData="{\"tongSolan\":"+tongSolan+",\"dungGio\":"+dunggio+",\"somTre\":"+somTre+",\"NDCB\":"+ndcb
					+",\"NDKB\":"+ndkb+",\"DB\":"+db+",\"CDB\":"+chuadb+"}";
		}
		System.out.println("JSONDATA : "+ jsonData);
		connectionOracle.closeConnection();
		return Action.SUCCESS;

	}
}
